﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Warensoft.Unity.Communication.Client.DataClient;
using Warensoft.Unity.Communication.Client;

namespace Warensoft.DataService.DBHelpers
{
    public class DataServiceDataAdapter
    {
        private string selectCommandText;
        private DBHelper dbHelper;
        private string connectionString;
        private DbCommand selectCommand;
        private DbDataAdapter adpt;
        private DbCommandBuilder commandBuilder;
        private DbConnection con;
        private DataTable schemaTable;
        private DbCommand clientWinsUpdateCommand;
        private DbCommand storeWinsUpdateCommand;
        private DbCommand clientWinsDeleteCommand;
        private DbCommand storeWinsDeleteCommand;
        public DataServiceDataAdapter(DataTable table, DBHelper dbHelper, string connectionString)
        {
            this.schemaTable = table;
            this.dbHelper = dbHelper;
            this.connectionString = connectionString;
            this.selectCommandText = string.Format("select * from [{0}]", table.TableName);

            this.con = dbHelper.CreateConnection();

            this.con.ConnectionString = connectionString;
            this.commandBuilder = dbHelper.CreateCommandBuilder();
            this.adpt = dbHelper.CreateDataAdapter();
            this.selectCommand = con.CreateCommand();
            this.selectCommand.CommandText = this.selectCommandText;
            this.adpt.SelectCommand = this.selectCommand;
            this.commandBuilder.DataAdapter = this.adpt;
            // builder.ConflictOption = ConflictOption.OverwriteChanges;
            this.adpt.InsertCommand = this.commandBuilder.GetInsertCommand(true);
            this.storeWinsUpdateCommand = this.commandBuilder.GetUpdateCommand(true);
            this.storeWinsDeleteCommand = this.commandBuilder.GetDeleteCommand(true);
            this.commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
            this.clientWinsUpdateCommand = this.commandBuilder.GetUpdateCommand(true);
            this.clientWinsDeleteCommand = this.commandBuilder.GetDeleteCommand(true);
            this.adpt.Fill(this.schemaTable);
        }

        public void Insert(List<DataEntity> insertedEntities)
        {
           
            foreach (var entity in insertedEntities)
            {
                var row =this.schemaTable.NewRow();
                foreach (var property in entity.Properties)
                {
                    row[property.ColumnName] = property.Value;
                }
                this.schemaTable.Rows.Add(row);
            }
            this.adpt.Update(this.schemaTable );
        }

        public void Delete(List<DataEntity> deletedEntities, RefreshMode mode)
        {
            switch (mode)
            {
                case RefreshMode.ClientWins:
                    this.adpt.DeleteCommand = this.clientWinsDeleteCommand;
                    break;
                case RefreshMode.StoreWins:
                    this.adpt.DeleteCommand = this.storeWinsDeleteCommand;
                    break;
                default:
                    break;
            }
            var rows = this.FindRows(deletedEntities);
            foreach (DataRow row in rows)
            {
                row.Delete();
            }
            this.adpt.Update(this.schemaTable);
        }

        public void Update(List<DataEntity> updateEntities, RefreshMode mode)
        {
            switch (mode)
            {
                case RefreshMode.ClientWins:
                    this.adpt.UpdateCommand = this.clientWinsUpdateCommand;
                    break;
                case RefreshMode.StoreWins:
                    this.adpt.UpdateCommand = this.storeWinsUpdateCommand;
                    break;
                default:
                    break;
            }
            var rows = this.FindRows(updateEntities);
            foreach (var entity in updateEntities)
            {
                foreach (var property in entity.Properties)
                {
                    foreach (var row in rows)
                    {
                        row[property.ColumnName] = property.Value;
                    }

                }
            }

            this.adpt.Update(this.schemaTable);
        }
        private IEnumerable<DataRow> FindRows(List<DataEntity> entities)
        {
            List<DataRow> resultRows = new List<DataRow>();
            foreach (var entity in entities)
            {
                IEnumerable<DataRow> rows = this.schemaTable.Rows.Cast<DataRow>();

                var keys = entity.Properties.Where(p => p.IsPrimaryKey);
                if (keys.Count() == 0)
                {
                    keys = entity.Properties.Cast<DataProperty>();
                }

                foreach (var key in entity.Properties)
                {


                    string columName = key.ColumnName;
                    object value = key.OriginalValue;
                    rows = rows.Where(row => row[key.ColumnName].Equals(key.OriginalValue));
                }

                resultRows.AddRange(rows );
            }
            return resultRows;
        }
        public DataSet GetData(DataQuery query)
        {
            var rows = this.QueryRows(query);
            DataTable table = rows.CopyToDataTable();
            foreach (var subQuery in query.MergedQueries)
            {
                var subRows = this.QueryRows(subQuery);
                var subTable = subRows.CopyToDataTable();
                table.Merge(subTable);
            }
            DataSet ds = new DataSet();
            ds.Tables.Add(table);
            return ds;
        }
        public IEnumerable<DataRow> QueryRows(DataQuery query)
        {
            var rows = this.schemaTable.Rows.Cast<DataRow>();

            var queries = query.Queries;
            foreach (var q in queries)
            {
                switch (q.Type)
                {
                    case QueryType.None:
                        break;
                    case QueryType.EqualsTo:
                        rows = QueryEqualsTo(rows, q);
                        break;
                    case QueryType.NotEqualsTo:
                        rows = QueryNotEqualsTo(rows, q);
                        break;
                    case QueryType.OrderBy:
                        rows = rows.OrderBy(row => row[q.Key]);
                        break;
                    case QueryType.OrderByDesc:
                        rows = rows.OrderByDescending(row => row[q.Key]);
                        break;
                    case QueryType.Take:
                        rows = rows.Take(q.Take);
                        break;
                    case QueryType.Skip:
                        rows = rows.Skip(q.Skip);
                        break;
                    case QueryType.GreaterThan:
                        rows = this.QueryGreaterThan(rows, q);
                        break;
                    case QueryType.GreaterThanOrEqualsTo:
                        rows = this.QueryGreaterThanOrEqualsTo(rows, q);
                        break;
                    case QueryType.SmallerThan:
                        rows = this.QuerySmallerThan(rows, q);
                        break;
                    case QueryType.SmallerThanOrEqualsTo:
                        rows = this.QuerySmallerThanOrEqualsTo(rows, q);
                        break;
                    default:
                        break;
                }
            }
            return rows;
        }
        private IEnumerable<DataRow> QueryEqualsTo(IEnumerable<DataRow> rows, QueryData query)
        {
            switch (query.TypeName)
            {
                case "Guid":
                    rows = rows.Where(row => ((Guid)row[query.Key]) == (Guid)query.Value);
                    break;
                case "Byte":
                    rows = rows.Where(row => ((byte)row[query.Key]) == (byte)query.Value);
                    break;
                case "Int16":
                    rows = rows.Where(row => ((short)row[query.Key]) == (short)query.Value);
                    break;
                case "Int32":
                    rows = rows.Where(row => ((int)row[query.Key]) == (int)query.Value);
                    break;
                case "Int64":
                    rows = rows.Where(row => ((long)row[query.Key]) == (long)query.Value);
                    break;
                case "UInt16":
                    rows = rows.Where(row => ((ushort)row[query.Key]) == (ushort)query.Value);
                    break;
                case "UInt32":
                    rows = rows.Where(row => ((uint)row[query.Key]) == (uint)query.Value);
                    break;
                case "UInt64":
                    rows = rows.Where(row => ((ulong)row[query.Key]) == (ulong)query.Value);
                    break;
                case "Double":
                    rows = rows.Where(row => ((double)row[query.Key]) == (double)query.Value);
                    break;
                case "Single":
                    rows = rows.Where(row => ((float)row[query.Key]) == (float)query.Value);
                    break;
                case "Decimal":
                    rows = rows.Where(row => ((decimal)row[query.Key]) == (decimal)query.Value);
                    break;
                case "Boolean":
                    rows = rows.Where(row => ((bool)row[query.Key]) == (bool)query.Value);
                    break;
                case "String":
                    rows = rows.Where(row => row[query.Key].ToString() == query.Value.ToString());
                    break;
                default:
                    break;
            }
            return rows;
        }
        private IEnumerable<DataRow> QueryNotEqualsTo(IEnumerable<DataRow> rows, QueryData query)
        {
            switch (query.TypeName)
            {
                case "Guid":
                    rows = rows.Where(row => ((Guid)row[query.Key]) != (Guid)query.Value);
                    break;
                case "Byte":
                    rows = rows.Where(row => ((byte)row[query.Key]) != (byte)query.Value);
                    break;
                case "Int16":
                    rows = rows.Where(row => ((short)row[query.Key]) != (short)query.Value);
                    break;
                case "Int32":
                    rows = rows.Where(row => ((int)row[query.Key]) != (int)query.Value);
                    break;
                case "Int64":
                    rows = rows.Where(row => ((long)row[query.Key]) != (long)query.Value);
                    break;
                case "UInt16":
                    rows = rows.Where(row => ((ushort)row[query.Key]) != (ushort)query.Value);
                    break;
                case "UInt32":
                    rows = rows.Where(row => ((uint)row[query.Key]) != (uint)query.Value);
                    break;
                case "UInt64":
                    rows = rows.Where(row => ((ulong)row[query.Key]) != (ulong)query.Value);
                    break;
                case "Double":
                    rows = rows.Where(row => ((double)row[query.Key]) != (double)query.Value);
                    break;
                case "Single":
                    rows = rows.Where(row => ((float)row[query.Key]) != (float)query.Value);
                    break;
                case "Decimal":
                    rows = rows.Where(row => ((decimal)row[query.Key]) != (decimal)query.Value);
                    break;
                case "Boolean":
                    rows = rows.Where(row => ((bool)row[query.Key]) != (bool)query.Value);
                    break;
                case "String":
                    rows = rows.Where(row => row[query.Key].ToString() != query.Value.ToString());
                    break;
                default:
                    break;
            }
            return rows;
        }
        private IEnumerable<DataRow> QueryGreaterThan(IEnumerable<DataRow> rows, QueryData query)
        {
            switch (query.TypeName)
            {
                case "Byte":
                    rows = rows.Where(row => ((byte)row[query.Key]) > (byte)query.Value);
                    break;
                case "Int16":
                    rows = rows.Where(row => ((short)row[query.Key]) > (short)query.Value);
                    break;
                case "Int32":
                    rows = rows.Where(row => ((int)row[query.Key]) > (int)query.Value);
                    break;
                case "Int64":
                    rows = rows.Where(row => ((long)row[query.Key]) > (long)query.Value);
                    break;
                case "UInt16":
                    rows = rows.Where(row => ((ushort)row[query.Key]) > (ushort)query.Value);
                    break;
                case "UInt32":
                    rows = rows.Where(row => ((uint)row[query.Key]) > (uint)query.Value);
                    break;
                case "UInt64":
                    rows = rows.Where(row => ((ulong)row[query.Key]) > (ulong)query.Value);
                    break;
                case "Double":
                    rows = rows.Where(row => ((double)row[query.Key]) > (double)query.Value);
                    break;
                case "Single":
                    rows = rows.Where(row => ((float)row[query.Key]) > (float)query.Value);
                    break;
                case "Decimal":
                    rows = rows.Where(row => ((decimal)row[query.Key]) > (decimal)query.Value);
                    break;


                default:
                    break;
            }
            return rows;
        }
        private IEnumerable<DataRow> QueryGreaterThanOrEqualsTo(IEnumerable<DataRow> rows, QueryData query)
        {
            switch (query.TypeName)
            {
                case "Byte":
                    rows = rows.Where(row => ((byte)row[query.Key]) >= (byte)query.Value);
                    break;
                case "Int16":
                    rows = rows.Where(row => ((short)row[query.Key]) >= (short)query.Value);
                    break;
                case "Int32":
                    rows = rows.Where(row => ((int)row[query.Key]) >= (int)query.Value);
                    break;
                case "Int64":
                    rows = rows.Where(row => ((long)row[query.Key]) >= (long)query.Value);
                    break;
                case "UInt16":
                    rows = rows.Where(row => ((ushort)row[query.Key]) >= (ushort)query.Value);
                    break;
                case "UInt32":
                    rows = rows.Where(row => ((uint)row[query.Key]) >= (uint)query.Value);
                    break;
                case "UInt64":
                    rows = rows.Where(row => ((ulong)row[query.Key]) >= (ulong)query.Value);
                    break;
                case "Double":
                    rows = rows.Where(row => ((double)row[query.Key]) >= (double)query.Value);
                    break;
                case "Single":
                    rows = rows.Where(row => ((float)row[query.Key]) >= (float)query.Value);
                    break;
                case "Decimal":
                    rows = rows.Where(row => ((decimal)row[query.Key]) >= (decimal)query.Value);
                    break;


                default:
                    break;
            }
            return rows;
        }

        private IEnumerable<DataRow> QuerySmallerThan(IEnumerable<DataRow> rows, QueryData query)
        {
            switch (query.TypeName)
            {
                case "Byte":
                    rows = rows.Where(row => ((byte)row[query.Key]) < (byte)query.Value);
                    break;
                case "Int16":
                    rows = rows.Where(row => ((short)row[query.Key]) < (short)query.Value);
                    break;
                case "Int32":
                    rows = rows.Where(row => ((int)row[query.Key]) < (int)query.Value);
                    break;
                case "Int64":
                    rows = rows.Where(row => ((long)row[query.Key]) < (long)query.Value);
                    break;
                case "UInt16":
                    rows = rows.Where(row => ((ushort)row[query.Key]) < (ushort)query.Value);
                    break;
                case "UInt32":
                    rows = rows.Where(row => ((uint)row[query.Key]) < (uint)query.Value);
                    break;
                case "UInt64":
                    rows = rows.Where(row => ((ulong)row[query.Key]) < (ulong)query.Value);
                    break;
                case "Double":
                    rows = rows.Where(row => ((double)row[query.Key]) < (double)query.Value);
                    break;
                case "Single":
                    rows = rows.Where(row => ((float)row[query.Key]) < (float)query.Value);
                    break;
                case "Decimal":
                    rows = rows.Where(row => ((decimal)row[query.Key]) < (decimal)query.Value);
                    break;


                default:
                    break;
            }
            return rows;
        }
        private IEnumerable<DataRow> QuerySmallerThanOrEqualsTo(IEnumerable<DataRow> rows, QueryData query)
        {
            switch (query.TypeName)
            {
                case "Byte":
                    rows = rows.Where(row => ((byte)row[query.Key]) <= (byte)query.Value);
                    break;
                case "Int16":
                    rows = rows.Where(row => ((short)row[query.Key]) <= (short)query.Value);
                    break;
                case "Int32":
                    rows = rows.Where(row => ((int)row[query.Key]) <= (int)query.Value);
                    break;
                case "Int64":
                    rows = rows.Where(row => ((long)row[query.Key]) <= (long)query.Value);
                    break;
                case "UInt16":
                    rows = rows.Where(row => ((ushort)row[query.Key]) <= (ushort)query.Value);
                    break;
                case "UInt32":
                    rows = rows.Where(row => ((uint)row[query.Key]) <= (uint)query.Value);
                    break;
                case "UInt64":
                    rows = rows.Where(row => ((ulong)row[query.Key]) <= (ulong)query.Value);
                    break;
                case "Double":
                    rows = rows.Where(row => ((double)row[query.Key]) <= (double)query.Value);
                    break;
                case "Single":
                    rows = rows.Where(row => ((float)row[query.Key]) <= (float)query.Value);
                    break;
                case "Decimal":
                    rows = rows.Where(row => ((decimal)row[query.Key]) <= (decimal)query.Value);
                    break;


                default:
                    break;
            }
            return rows;
        }

    }
}
